All the Perl that's Practical to Extract and Report

Oracle Package Ownership

Mr. Muskrat on 2007-04-11T18:47:45

In Oracle, everything in the database is an object with many properties. One of these properties is OWNER. To determine the owner of a particular object, we can query the DBA_SOURCE table. In this case, we are interested in the owner of the package in which a function is defined so we could run this query:

SQL> SELECT OWNER FROM DBA_SOURCE WHERE TYPE = 'PACKAGE' AND NAME = 'SOME_PACKAGE' GROUP BY OWNER ORDER BY OWNER;

OWNER
------------------------------
ABCDEV
XYZDEV

Here we see that you can have objects that share a name with different owners.

Alternately we could query against DBA_OBJECTS:

SQL> SELECT OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND OBJECT_NAME = 'SOME_PACKAGE' GROUP BY OWNER ORDER BY OWNER;

OWNER
------------------------------
ABCDEV
XYZDEV

The problem with those approaches is that the owner of the package must be a DBA.

USER will always be the user who is running the query and therefore cannot be used to determine ownership of the package during the call to the function. Assuming the owner is the same as the schema (which it is in most cases) then the following query will get us the owner of a package:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

Here is a short package to test it:

CREATE OR REPLACE PACKAGE FOO
AS
  FUNCTION BAR RETURN NUMBER;
END FOO;
/

CREATE OR REPLACE PACKAGE BODY FOO
IS
  FUNCTION BAR RETURN NUMBER
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'));
    RETURN 1;
  END;
  
END FOO;
/

Load the package as one user (XYZDEV for example), grant execute on the package to a second user (GRANT EXECUTE ON FOO TO ABCDEV), log in as the second user and run it.

SQL> SET SERVEROUTPUT ON
SQL> SELECT FOO.BAR() FROM DUAL;

 FOO.BAR()
----------
         1

XYZDEV

Any questions?

Besides dba_*

dhorne on 2007-04-11T22:30:19

What about using all_source or all_objects?

Re:Besides dba_*

Mr. Muskrat on 2007-04-11T22:42:53

I had forgotten about those but most DBAs are reluctant to give grant select on any of the SYS owned stuff if they can help it.

Re:Besides dba_*

dhorne on 2007-04-11T22:49:41

The all_* virtual tables should give you access to everything you own (as with user_*), plus what you have been granted by other users.

To be honest, I wasn't aware of SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'). Good to add to my bag o' tricks ;-)

Re:Besides dba_*

Mr. Muskrat on 2007-04-12T00:14:07

I knew about SYS_CONTEXT because that's part of the magic behind Virtual Private Databases. I designed and coded one at work.

Re:Besides dba_*

jdavidb on 2007-04-13T15:17:45

This doesn't work for the scenario for which I needed this. I have two identical packages in two schemas. Same name, same lines of code. I need the code in the package to determine the name of the package owner and query a table looking for rows where a field is set to the name of the owner.

If I query one of the _SOURCE views (or better yet, one of the _OBJECT views), I'll find both packages and both owners, and still have no way of knowing which one I'm running in. :)